#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include "sqlite3.h"
#include "cJSON.h"
#include "XLSX.h"

#define O_BINARY 0

//#include <io.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <unistd.h>

#include "xlsxio_read.h"
#include "xlsxio_write.h"

#if !defined(XML_UNICODE_WCHAR_T) && !defined(XML_UNICODE)
//UTF-8 version
#define X(s) s
#define XML_Char_printf printf
#else
//UTF-16 version
#define X(s) L##s
#define XML_Char_printf wprintf
#endif

typedef struct __sheet_property
{ 
  int           nRow;
  int           nCol;
  char          name[32];
  xlsxioreadersheet  handle;
  XLSXIOCHAR    CELLBUFS[32][256];
  char          xlsxFile[256];
  int           xlsxHandle;
  xlsxioreader  xlsxReaderHandle;

  char          header[32][256];
  JSON_SHEET*   cfgval;
  char          itrsec[256];          //[可选项]多表连接查询用到的交集字段，避免重复插入操作
}SHEET;

typedef struct __object_xlsx
{
    char          xlsxFile[256];
    char          sheetName[256];
    xlsxiowriter xlsxWriterHandle;
    #if 0
    char          header[32][256];
    #endif
}OBJXLSX;


OBJXLSX objxlsx;

void format_sheet_header(SHEET* sheet)
{
  int i;
  for(i = 0; i < sheet->cfgval->nColumns; i++)
  {
    char buffer[1024]; strcpy(buffer, sheet->header[i]);
    int j,k; int len = strlen(buffer);
    for(j = 0,k = 0; j < len; j++)
    {
      if(buffer[j] == ' ')            //空格' ' 直接转换为下划线 '_'
      {
        sheet->header[i][k++] = '_';
      }
      else if(buffer[j] == '#')       //忽略 '#'
      {
        continue;
      }
      else{
        sheet->header[i][k++] = buffer[j];
      }
       
    }
    sheet->header[i][k] = '\0';
  }
}

int callback_read_row(SHEET* sheet, sqlite3 *db)
{
    XLSXIOCHAR* cell_value;
    int C = 0; int ColumnUsed = 0;
    while ((cell_value = xlsxioread_sheet_next_cell(sheet->handle)) != NULL) 
    {
      if(sheet->nRow == 0) 
      { 
        if(ColumnUse(sheet->cfgval->ColumnsBitArray, C))
        {
          strcpy(sheet->header[ColumnUsed], cell_value);
          ColumnUsed++;
        }
        sheet->nCol++; 
      }
      //XML_Char_printf(X("%s\t"), cell_value);
      //printf("%s\t", cell_value);
      else
      {
        if(ColumnUse(sheet->cfgval->ColumnsBitArray, C))
        {
          strcpy(sheet->CELLBUFS[ColumnUsed], cell_value);
          ColumnUsed++;
        }
      }
      C++;
      free(cell_value);
    }

    sheet->nRow++;      //Excel Sheet行计数值

    char sql[4096]; char *zErrMsg =NULL;
    /*
    create table tab1(
      PartNo TEXT PRIMARY KEY,
      Pin    INTEGER,
      Price  REAL,
      Qty    INTEGER,
      State  TEXT
    );
    */
    if(sheet->nRow == 1)  //Excel Sheet头，创建Sheet对应的SQLite3表
    {
      format_sheet_header(sheet);
      sprintf(sql, "create table %s(", sheet->cfgval->name);
      for(C=0;C<ColumnUsed;C++){
        if(strcmp(sheet->itrsec, sheet->header[C]) == 0)    //[可选项]多表连接查询用到的交集字段 作为表的主键，避免重复插入操作
        {
          strcat(sql, sheet->header[C]); strcat(sql, "  TEXT PRIMARY KEY");
        }else{
          strcat(sql, sheet->header[C]); strcat(sql, "  TEXT");
        }
        if(C!=(ColumnUsed-1))
          strcat(sql, ", ");
      }
      strcat(sql, ");");
        
        //printf("%s\n", sql);
    }
    /* insert into tab1(PartNo, Pin, Price, Qty, State) values('cc1101', 20, 5.3, 30, 'OLD');
    */
    else if(sheet->nRow > 1) //Excel Sheet数据，插入数据到Sheet对应的SQLite3表
    {
      sprintf(sql, "insert into %s(", sheet->cfgval->name);
      for(C=0;C<ColumnUsed;C++){
        strcat(sql, sheet->header[C]); 
        if(C!=(ColumnUsed-1))
            strcat(sql, ", ");
      }
      strcat(sql, ") values(");
      for(C=0;C<ColumnUsed;C++)
      {
        strcat(sql, "'");strcat(sql, sheet->CELLBUFS[C]);strcat(sql, "'");
        if(C!=(ColumnUsed-1))
          strcat(sql, ", ");
      }
      strcat(sql, ");");

          //printf("%s\n", sql);
    }
    sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);
    sqlite3_free(zErrMsg);

    return 0;
}

int sheet_read_all_cell(SHEET* sheet, sqlite3 *db, int (*cbfunc_row)(SHEET* _sheet, sqlite3 *_db) )
{
    if ((sheet->xlsxHandle = open(sheet->xlsxFile, O_RDONLY | O_BINARY, 0)) == -1) {
      fprintf(stderr, "Error opening .xlsx file\n");
      return 1;
    }
    if ((sheet->xlsxReaderHandle = xlsxioread_open_filehandle(sheet->xlsxHandle)) == NULL) {
      fprintf(stderr, "Error reading .xlsx file\n");
      return 2;
    }

    
    sheet->handle = xlsxioread_sheet_open(sheet->xlsxReaderHandle, sheet->name, XLSXIOREAD_SKIP_EMPTY_ROWS);
    sheet->nRow = 0; sheet->nCol = 0;

    while (xlsxioread_sheet_next_row(sheet->handle)) 
    {
        cbfunc_row(sheet, db);
    }

    xlsxioread_sheet_close(sheet->handle);

    if(sheet->xlsxReaderHandle != NULL)
      xlsxioread_close(sheet->xlsxReaderHandle);
    if(sheet->xlsxHandle != 0)
      close(sheet->xlsxHandle);

    return 0;
}


void print_config(CFG_VAR* cfgvar)
{
  printf("Excel file: %s\n", cfgvar->xlsx);
  //printf("Total %d sheets.\n", cfgvar->nSheet);
  int i,j;
  for(i = 0; i < cfgvar->nSheet; i++)
  {
    printf("Sheet[%s]'s Columns: ", cfgvar->sheets[i].name);
    for(j = 0; j < cfgvar->sheets[i].nColumns; j++)
    {
      printf("%s\t", cfgvar->sheets[i].columns[j]);
    }
    printf("\n");
  }
  printf("SQL: %s\n", cfgvar->sql);
  printf("Intersection field: %s\tlength:%d\n", cfgvar->itrsec, strlen(cfgvar->itrsec));
}

void load_config(char* fname, CFG_VAR* cfgvar)
{
  FILE* fp = NULL; 
  char* stream; long txtlen = 0;
  cJSON *json;
  if((fp=fopen(fname, "r")) == NULL)  //尝试读文件
  {
    fprintf(stderr, "cJSON/> {INFO} Sheet columns config file '%s' load faild\n", fname);
  }
  else
  {
    txtlen = fstr_alloc(fp, &stream);
    //printf("%s\n", stream);
    json = cJSON_Parse(stream);

    /* 从配置文件中解析出参数 */
    strcpy(cfgvar->xlsx, cJSON_GetObjectItem(json, "xlsx")->valuestring);
    
    cJSON* sheet = cJSON_GetObjectItem(json, "sheet");
    cfgvar->nSheet = cJSON_GetArraySize(sheet);
    int i,j;
    for(i = 0; i < cfgvar->nSheet; i++)
    {
      cJSON *sheet_item = cJSON_GetArrayItem(sheet, i);
      strcpy(cfgvar->sheets[i].name, cJSON_GetObjectItem(sheet_item, "name")->valuestring);

      cJSON *columns  = cJSON_GetObjectItem(sheet_item, "columns");
      cfgvar->sheets[i].nColumns = cJSON_GetArraySize(columns);

      for(j = 0; j < cfgvar->sheets[i].nColumns; j++)
      {
        strcpy(cfgvar->sheets[i].columns[j], cJSON_GetArrayItem(columns, j)->valuestring);
        Mark(cfgvar->sheets[i].ColumnsBitArray, letter_to_int(cfgvar->sheets[i].columns[j])); 
      }
    }

    
    strcpy(cfgvar->sql, cJSON_GetObjectItem(json, "sql")->valuestring);
    strcpy(cfgvar->itrsec, cJSON_GetObjectItem(json, "itrsec")->valuestring);
    strcpy(cfgvar->objxlsx, cJSON_GetObjectItem(json, "objxlsx")->valuestring);
    strcpy(cfgvar->objsheet, cJSON_GetObjectItem(json, "objsheet")->valuestring);
    #if 0
      cJSON *objhead  = cJSON_GetObjectItem(json, "objhead");
      cfgvar->nObjhead = cJSON_GetArraySize(objhead);

      for(j = 0; j < cfgvar->nObjhead; j++)
      {
        strcpy(cfgvar->objhead[j], cJSON_GetArrayItem(objhead, j)->valuestring);
      }
    #endif
    cJSON_Delete(json);
    fstr_free(stream);
    fclose(fp); fp=NULL;
    fprintf(stderr, "cJSON/> {INFO} Sheet columns config file '%s' load success.\n", fname);
  }
}


int db_print_result_set_to_xlsx(OBJXLSX* xlsx, char **azResult, int nrow, int ncolumn)
{

    int i;
    printf("nrow=%d ncolumn=%d\n", nrow, ncolumn);
   if ((xlsx->xlsxWriterHandle = xlsxiowrite_open(xlsx->xlsxFile, xlsx->sheetName)) == NULL) {
    fprintf(stderr, "Error creating .xlsx file\n");
    return 1;
  }
  //set row height
  xlsxiowrite_set_row_height(xlsx->xlsxWriterHandle, 1);
  //how many rows to buffer to detect column widths
  xlsxiowrite_set_detection_rows(xlsx->xlsxWriterHandle, 10);


    /* db查询结果集中已经包含Excel Sheet头名称
      field name: azResult[0] ~ azResult[ncolumn-1] */
  for(i = 0; i < ncolumn; i++)
  {
    xlsxiowrite_add_column(xlsx->xlsxWriterHandle, azResult[i], 0);
  }
  xlsxiowrite_next_row(xlsx->xlsxWriterHandle);

  /* 写入数据
     DATA: azResult[ncolumn] ~ azResult[(nrow+1)*ncolumn-1] */
    for(i=ncolumn; i < (nrow+1)*ncolumn; i++)
    {
      printf("%s\t", azResult[i]); if((i+1)%ncolumn == 0){ printf("\n"); }
      xlsxiowrite_add_cell_string(xlsx->xlsxWriterHandle, azResult[i]);
      //注意Excel写入过程 必须准确换行
      if((i+1)%ncolumn == 0)    
        xlsxiowrite_next_row(xlsx->xlsxWriterHandle);
    }

    xlsxiowrite_close(xlsx->xlsxWriterHandle);

    return 0;
}

int main (int argc, char* argv[])
{

  CFG_VAR cfgvar;
  load_config("../Col.cfg", &cfgvar);
  print_config(&cfgvar);

  sqlite3 *DB = NULL;
  int ret = sqlite3_open("cache", &DB);
  if(ret)
  {
      printf("can not open database.\n");
  }
  else
  {
      printf("open database succsee.\n");
  }
  
  SHEET *sheet = (SHEET*)malloc(sizeof(SHEET) * cfgvar.nSheet);
  int i;
  for(i = 0; i < cfgvar.nSheet; i++)
  {
    sheet[i].cfgval = &(cfgvar.sheets[i]);
    strcpy(sheet[i].xlsxFile, cfgvar.xlsx);
    strcpy(sheet[i].name, cfgvar.sheets[i].name);
    strcpy(sheet[i].itrsec, cfgvar.itrsec);
    sheet_read_all_cell(&sheet[i], DB, callback_read_row);
    //printf("%s->%s row[%d], col[%d]\n", sheet[i].xlsxFile, sheet[i].name, sheet[i].nRow, sheet[i].nCol);
  }

#if 0
  for(i = 0; i < cfgvar.nObjhead; i++)
  {
    strcpy(objxlsx.header[i], cfgvar.objhead[i]);
  }
#endif
  strcpy(objxlsx.xlsxFile, cfgvar.objxlsx);
  strcpy(objxlsx.sheetName, cfgvar.objsheet);

  /* 合并Sheet对应的数据表 
    SELECT vInfo.VM, vNetwork.IP_Address, vInfo.OS_according_to_the_configuration_file, vInfo.VM_UUID FROM vInfo LEFT JOIN vNetwork ON vInfo.VM_UUID=vNetwork.VM_UUID ORDER BY vInfo.VM;
  */
    int nrow=0; int ncolumn = 0;
    char *zErrMsg =NULL;
    char **azResult=NULL; //二维数组存放结果

        sqlite3_get_table( DB , cfgvar.sql , &azResult , &nrow , &ncolumn , &zErrMsg );

    /* Call back function running... */
        db_print_result_set_to_xlsx(&objxlsx, azResult, nrow, ncolumn);


    sqlite3_free(zErrMsg);

  if(sheet != NULL)
    free(sheet);
  sqlite3_close(DB);
}
